﻿drop table requisitions;
drop table items;
drop table users;
drop  table permissions;
drop TABLE types;
drop table locations;
drop TABLE images
drop TABLE companys;


CREATE TABLE companys (
	id_company int primary key Identity,
	name varchar(200) NOT NULL,
	website varchar(200) NOT NULL,
	active bit NOT NULL
) 

CREATE TABLE images(
	id_image 		INT 			NOT NULL IDENTITY ,
	name 			VARCHAR( 100 ) 	NOT NULL ,
	filename_extension		VARCHAR( 100 ) 	NOT NULL ,
	PRIMARY KEY (id_image ) 
)

create table locations(
 id_location int primary key identity,
 name varchar(200) not null,
 description varchar(200) not null,
 id_image int not null,
 belongs_to int not null,
 foreign key (id_image) REFERENCES images (id_image))

CREATE TABLE types (
	id_type int primary key Identity,
	id_image int NOT NULL,
	name nvarchar (500) NOT NULL	
	FOREIGN KEY (id_image) REFERENCES images (id_image)
) 

create  table permissions (
	id_permissions	INT not null IDENTITY,
	permission		VARCHAR (100) NOT NULL,
	description		VARCHAR (400) NOT NULL,
	PRIMARY KEY(id_permissions) 
)


create table items(
	id_item int primary key identity,
	Name varchar(200) not null,
	amount int not null,
	description varchar(200) not null,
	id_image int not null,
	id_type int not null,
	id_location int not null,
	date datetime not null,
	active bit not null,   
	FOREIGN KEY (id_image) REFERENCES images(id_image),
	FOREIGN KEY (id_type) REFERENCES types(id_type),
	FOREIGN KEY (id_location) REFERENCES locations(id_location)
)


create table users (
	id_user int not null primary key identity,
	name nvarchar(200) not null,
	username nvarchar(200) not null,
	pass nvarchar(200) not null,
	active bit not null,
	email nvarchar(200) not null,
	id_permissions int not null,
	foreign key(id_permissions) references permissions(id_permissions)
);


create table requisitions (
	id_requisition int not null primary key identity,
	id_item int not null,
	id_user int not null,
	date_rec datetime not null,
	delivered datetime not null,
	foreign key(id_item) references items(id_item),
	foreign key(id_user) references users(id_user)
);

GO

INSERT INTO companys(name,website,active)
VALUES('ESOH','www.esoh.pt',1);

INSERT INTO images(name,filename_extension)     
VALUES('Imagem 1','image.jpg');

INSERT INTO locations(name,description,id_image,belongs_to) 
VALUES('Joao','brito',1,1);

INSERT INTO types(id_image,name) 
VALUES (1,'hardware');

INSERT INTO permissions(permission,description)
VALUES('admininistrador','so o administrador ter todos os direitos');

INSERT INTO users(name,username,pass,active,email,id_permissions)
     VALUES('ze','toze','1234',1,'toze@email.com',1);

INSERT INTO items(Name,amount,description,id_image,id_type,id_location,date,active)     
VALUES('Diogo',1,'descrição de qualquer coisa',1,1,1,GETDATE(),0);

INSERT INTO requisitions(id_item,id_user,date_rec,delivered)     
VALUES(1,1,GETDATE(),GETDATE());